if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetQuestionnaireNameForProcess]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetQuestionnaireNameForProcess]
GO

/***************************************************************************
		Name:					GetQuestionnaireNameForProcess

		Purpose:				This Stored Procedure retrieves the Questionnaire 
								Name for the new or existing ProtocolProcessId
		PARAMETERS
		Name					Description					
		 -------------			-----------------------------------------------------------
		@protocolProcessId int	Process Id for the Protocol
		@name varchar			name of the process if needs to be found.
		-----------
		Returns
		Name of the Questionnaire
***************************************************************************/

CREATE Procedure dbo.GetQuestionnaireNameForProcess
(  
	@protocolProcessId int,
	@name varchar(255) = null
)  
AS   
BEGIN  

	SET NOCOUNT ON
	
	SELECT qa.Name 
	FROM ProtocolProcess pp
	INNER JOIN ProtocolVersion pv
	ON pv.ProtocolProcessId = pp.id
	INNER JOIN ProtocolVersion_QuestionnaireAnswer_Map pvm
	ON pv.Id = pvm.ProtocolVersionId
	INNER JOIN QuestionnaireAnswer qa
	ON qa.Id = pvm.QuestionnaireAnswerId
	WHERE pp.Id = @protocolProcessId
	AND qa.Name like IsNull(@name, '%%')	
	
END

GO

